import type { Router } from "express"
import type { Ioracle, IcallbackFn } from "../../types"
import { sendMail } from "./mail"
import { SUCCESS_RESULT, ERROR_RESULT } from "../../utils"

export default (router: Router, oracle: Ioracle, callbackFn: IcallbackFn) => {
  router.post("/getAssetDept", (req, res) => {
    const { orgCode } = req.body
    let sql = " select distinct dept_code "
    sql += " from xx_general_asset_equip_list "
    sql += " where ( dept_code like '" + orgCode + "%' "
    sql +=
      " or (dept_code in ('FPCIO','SMTIO') and '" + orgCode + "' = 'CH') ) "
    callbackFn(res, oracle.connChaos, { sql })
  })

  //抓取课别
  router.post("/getDept", (req, res) => {
    const { orgCode } = req.body
    let sql = " select code "
    sql += " from department "
    sql += " where 1 = 1 "
    sql += " and status = 'N' "
    sql += " and code like '" + orgCode + "%' "

    callbackFn(res, oracle.connChaos, { sql })
  })

  //抓取人员
  router.post("/getUserInfo", (req, res) => {
    const { deptCode } = req.body
    let sql = " select work_num,chinese_name "
    sql += " from user_info_n "
    sql += " where 1 = 1 "
    sql += " and job_status = 'employed' "
    sql += " and dept like '" + deptCode + "%' "
    callbackFn(res, oracle.connHrm, { sql })
  })

  //财产编号
  router.post("/getAssetNo", (req, res) => {
    const { deptCode, assetType, power, account } = req.body
    let sql = ""
    sql +=
      " select distinct asset_no,asset_name,to_char(get_date,'yyyy/mm/dd') get_date "
    sql +=
      " ,to_char(start_date,'yyyy/mm/dd') start_date,to_char(end_date,'yyyy/mm/dd') end_date "
    sql += " ,asset_type "
    sql += " from xx_general_asset_equip_list "
    sql += " where 1 = 1 "
    sql += " and dept_code like '" + deptCode + "%' "
    sql += " and asset_type = '" + assetType + "' "
    if (power !== "Admin") {
      sql += " and dept_code in ( "
      sql += " select asset_dept from xx_general_asset_manager  "
      sql += " where account = '" + account + "'  ) "
    }
    sql += " order by asset_no "
    callbackFn(res, oracle.connChaos, { sql })
  })

  //抓取盘点报告盘点人员
  router.post("/getKlmInfo", (req, res) => {
    const { headerId } = req.body
    let sql = " select c.work_num, c.chinese_name, a.dept_code "
    sql += " ,c.dept,a.check_month,a.status,a.flow_phase "
    sql += " from xx_general_asset_check_header a "
    sql += " ,xx_general_asset_check_flow b, user_info_n c "
    sql += " where a.header_id = b.header_id "
    sql += " and a.header_id = '" + headerId + "' "
    sql += " and b.current_seq = '10' "
    sql += " and b.action_klm = c.account "
    callbackFn(res, oracle.connChaos, { sql })
  })

  //盘点报告细项
  router.post("/getReportDetail", (req, res) => {
    const { headerId } = req.body
    let sql = " select asset_type,nvl(total_cnt,0) total_cnt "
    sql += " ,nvl(check_cnt,0) check_cnt "
    sql += " ,nvl(total_cnt,0) - nvl(check_cnt,0) leave_cnt "
    sql += " from "
    sql += " (select 'A類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'A-%' "
    sql += " union "
    sql += " select 'B類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'B-%' "
    sql += " union "
    sql += " select 'C類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'C-%' "
    sql += " union "
    sql += " select 'D類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'D-%' "
    sql += " union "
    sql += " select 'E類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'E-%' "
    sql += " union "
    sql += " select 'F類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'F-%' "
    sql += " union "
    sql += " select 'G類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'G-%' "
    sql += " union "
    sql += " select 'H類' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'A' "
    sql += " and asset_no like 'H-%' "
    sql += " union "
    sql += " select 'Exist' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'Exist' "
    sql += " union "
    sql += " select 'Rent' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'Rent' "
    sql += " union "
    sql += " select 'Free' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'Free' "
    sql += " union "
    sql += " select 'Demo' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'Demo' "
    sql += " union "
    sql += " select 'Offer' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "') "
    sql += " and asset_type = 'Offer' "
    sql += " union "
    sql += " select 'Total' asset_type "
    sql += " ,sum(decode(asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_line "
    sql += " where header_id in ('" + headerId + "')) "
    sql += " order by decode (asset_type,'A類', 0,'B類', 1 "
    sql += " ,'C類', 2,'D類', 3,'E類', 4 "
    sql += " ,'F類',5,'G類',6,'H類',7 "
    sql += " ,'Exist', 8,'Rent', 9,'Free', 10,'Demo' "
    sql += " , 11,'Offer', 12,'Total', 13,14) "
    callbackFn(res, oracle.connChaos, { sql })
  })

  //签核流程
  router.post("/getSignFlow", (req, res) => {
    const { headerId } = req.body
    let sql = " select a.action_dept,b.chinese_name "
    sql += " ,to_char(a.updated_date,'yyyy/mm/dd hh24:mi:ss') updated_date "
    sql +=
      " ,decode(a.sign_flag,'Y','同意','N','退回','') sign_flag,a.sign_comment,a.flow_phase "
    sql += " from xx_general_asset_check_flow a,user_info_n b "
    sql += " where a.action_klm = b.account "
    sql += " and a.header_id = '" + headerId + "' "
    sql += " order by a.current_seq "
    callbackFn(res, oracle.connChaos, { sql })
  })

  //人员签核
  router.post("/signList", (req, res) => {
    const { headerId, signFlag, signComment, account } = req.body


    let sql = ` begin xx_general_asset_pkg.xx_general_asset_main('${headerId}','${signFlag}','${signComment}','${account}');end; `
    oracle
      .connChaos({ sql })
      .then(() => {
        //发送邮件
        sql =
          " select case when substr(b.account,1,1) = 'x' then substr(b.account,9)||'@compeq.huizhou.gd.cn' "
        sql +=
          " when substr(b.account,1,1) = 'p' then substr(b.account,9)||'@compeq.com.tw' "
        sql +=
          " when substr(b.account,1,1) = 'q' then substr(b.account,9)||'@compeq-cq.com.cn' "
        sql +=
          " when substr(b.account,1,1) = 's' then substr(b.account,9)||'@compeq-sz.com.cn' "
        sql += " end as email,b.chinese_name "
        sql += " ,substr(c.created_by,9)||'@compeq.huizhou.gd.cn' email1 "
        sql +=
          " from xx_general_asset_check_flow a,user_info_n b,xx_general_asset_check_header c "
        sql += " where a.header_id = '" + headerId + "' "
        sql += " and a.header_id = c.header_id "
        sql += " and a.action_klm = b.account "
        sql += " and a.sign_flag is null "
        sql += " and b.job_status = 'employed' "
        oracle
          .connChaos({ sql })
          .then((result) => {
            if (result.data.length) {
              // const {  chinese_name } = result.data[0]
              //同意
              const { email, chinese_name, email1 } = result.data[0]
              const href =
                "http://home70.compeq.huizhou.gd.cn/AppOracletest/general_asset/assetManage/asset_approve_report.html?dataType=approve&headerId=" +
                headerId
              sendMail({
                mailTo: email + ";" + email1,
                mailSubject: "固定資產系統-簽核通知郵件",
                mailHtml: "./mail/generalAsset/signList.ejs",
                mailData: { chinese_name, href, headerId, signFlag },
              })
                .then(() => res.send(SUCCESS_RESULT))
                .catch((err) => {
                  res.send({ code: 0, message: err })
                })
            } else {
              res.send(SUCCESS_RESULT)
            }
          })
          .catch(() => {
            res.send(ERROR_RESULT)
          })
      })
      .catch(() => {
        res.send(ERROR_RESULT)
      })
  })

  //抓取盘点进度
  router.post("/getAssetProcess", (req, res) => {
    const { checkMonth } = req.body

    let sql = " select substr(dept_code,1,2) org_code,dept_code "
    sql += " ,check_month,total_cnt,check_cnt "
    sql += " ,total_cnt-check_cnt leave_cnt "
    sql += " ,round(check_cnt/total_cnt,2)*100||'%' rate,status "
    sql += " from ( "
    sql += " select a.dept_code,a.check_month,a.status "
    sql += " ,sum(decode(b.asset_no,null,0,1)) total_cnt "
    sql += " ,sum(decode(b.check_result,null,0,1)) check_cnt "
    sql += " from xx_general_asset_check_header a "
    sql += " ,xx_general_asset_check_line b "
    sql += " where a.header_id = b.header_id "
    sql += " group by a.header_id,a.dept_code,a.check_month,a.status) "
    sql += " where check_month = '" + checkMonth + "' "
    callbackFn(res, oracle.connChaos, { sql })
  })

  //抓取盘差
  router.post("/getAssetAbnormal", (req, res) => {
    const { checkMonth } = req.body

    let sql = ""
    sql += " select substr(a.dept_code,1,2) org_code,a.dept_code "
    sql += " ,b.asset_no,b.asset_name "
    sql += " ,decode(b.asset_type,'A','已入籍','Rent','租賃' "
    sql += " ,'Offer','客供','Exist','待驗收' "
    sql += " ,'Free','無償借用',b.asset_type) asset_type "
    sql += " ,b.lose_reason,b.lose_action,b.upload_file "

    sql +=
      " from xx_general_asset_check_header a, xx_general_asset_check_line b "
    sql += " where a.header_id = b.header_id "
    sql += " and a.check_month = '" + checkMonth + "' "
    sql += " and a.flow_phase = 'close' "
    sql += " and a.check_type = '盤差報告' "
    sql += " and b.check_result is null "
    sql += " and a.dept_code not in ('CHG11','CCG11','CFG11')"

    // let sql = " select substr(dept_code,1,2) org_code,dept_code "
    // sql += " ,check_month,total_cnt,check_cnt "
    // sql += " ,total_cnt-check_cnt leave_cnt "
    // sql += " ,round(check_cnt/total_cnt,2)*100||'%' rate,status "
    // sql += " from ( "
    // sql += " select a.dept_code,a.check_month,a.status "
    // sql += " ,sum(decode(b.asset_no,null,0,1)) total_cnt "
    // sql += " ,sum(decode(b.check_result,null,0,1)) check_cnt "
    // sql += " from xx_general_asset_check_header a "
    // sql += " ,xx_general_asset_check_line b "
    // sql += " where a.header_id = b.header_id "
    // sql += " group by a.header_id,a.dept_code,a.check_month,a.status) "
    // sql += " where check_month = '" + checkMonth + "' "
    callbackFn(res, oracle.connChaos, { sql })
  })

  //抓取汇总资料
  router.post("/getAssetSummary", (req, res) => {
    const { checkMonth, preMonth, orgCode } = req.body
    let sql = " select a.*,nvl(b.cnt,0) new_cnt "
    sql += " ,(select count(*) from xx_general_asset_equip_list_t "
    sql += " where check_month = '" + checkMonth + "' "
    if (orgCode !== "ALL") {
      sql += " and (substr(dept_code,1,2) = '" + orgCode + "' "
      sql +=
        " or dept_code in ('SMTIO','FPCIO') and '" + orgCode + "' = 'CH' ) "
    }
    sql += " and asset_type = 'Exist' ) cnt1 "
    sql += " ,(select count(*) from xx_general_asset_equip_list_t "
    sql += " where check_month = '" + checkMonth + "' "
    if (orgCode !== "ALL") {
      sql += " and (substr(dept_code,1,2) = '" + orgCode + "' "
      sql +=
        " or dept_code in ('SMTIO','FPCIO') and '" + orgCode + "' = 'CH' ) "
    }
    sql += " and asset_type = 'Free' ) cnt2 "
    sql += " ,(select count(*) from xx_general_asset_equip_list_t "
    sql += " where check_month = '" + checkMonth + "' "
    if (orgCode !== "ALL") {
      sql += " and (substr(dept_code,1,2) = '" + orgCode + "' "
      sql +=
        " or dept_code in ('SMTIO','FPCIO') and '" + orgCode + "' = 'CH' ) "
    }
    sql += " and asset_type = 'Offer' ) cnt3 "
    sql += " ,(select count(*) from xx_general_asset_equip_list_t "
    sql += "  where check_month = '" + checkMonth + "' "
    if (orgCode !== "ALL") {
      sql += " and (substr(dept_code,1,2) = '" + orgCode + "' "
      sql +=
        " or dept_code in ('SMTIO','FPCIO') and '" + orgCode + "' = 'CH' ) "
    }
    sql += "  and asset_type = 'Rent' ) cnt4 "
    sql += " ,(select count(*) from xx_general_asset_equip_list_t "
    sql += "  where check_month = '" + checkMonth + "' "
    if (orgCode !== "ALL") {
      sql += " and (substr(dept_code,1,2) = '" + orgCode + "' "
      sql +=
        " or dept_code in ('SMTIO','FPCIO') and '" + orgCode + "' = 'CH' ) "
    }
    sql += " and asset_type = 'Demo' ) cnt5 "
    sql += " ,(select count(*) "
    sql += " from workflow.CPDPK00075_CPDF000192_DOCS@fpgprod a "
    sql += " ,workflow.RO_DOCUMENTDATA@fpgprod b "
    sql += " where a.txtcreatedate >= '" + checkMonth + "'||'/01' "
    sql += " and a.docid = b.docid "
    if (orgCode !== "ALL") {
      sql += " and a.txtfact = '" + orgCode + "' "
    }
    sql += " and b.procstatusid in ('S0104','S0105')) cnt6 "
    sql += " ,0 cnt7,0 cnt8,0 cnt9,0 cnt10,0 cnt11 "
    sql += " from ( "
    sql += " select n1,count(*) cnt "
    sql += " ,sum(asset_cost) total_cost "
    sql += " ,sum(remain_money) total_remain "
    sql += " ,sum(tax_cnt) tax_cnt "
    sql += " from ( "
    sql += " select substr(a.asset_no,1,1) n1,a.* "
    sql += " ,decode(b.tax_type,'Y',1,0) tax_cnt "
    sql += " from xx_general_asset_equip_list_t a,xx_general_asset_location b "
    sql += " where a.asset_type = 'A' "
    sql += " and a.check_month = '" + checkMonth + "' "
    if (orgCode !== "ALL") {
      sql += " and (substr(a.dept_code,1,2) = '" + orgCode + "' "
      sql +=
        " or a.dept_code in ('SMTIO','FPCIO') and '" + orgCode + "' = 'CH' ) "
    }
    sql += " and a.asset_no = b.asset_no(+)) "
    sql += " group by n1 "
    sql += " order by n1 ) a,(select substr(asset_no,1,1) n1,count(*) cnt "
    sql += " from xx_general_asset_equip_list_t a "
    sql += " where 1 = 1 "
    if (orgCode !== "ALL") {
      sql += " and (substr(a.dept_code,1,2) = '" + orgCode + "' "
      sql +=
        " or a.dept_code in ('SMTIO','FPCIO') and '" + orgCode + "' = 'CH' ) "
    }
    sql += " and a.check_month = '" + checkMonth + "' "
    sql += " and not exists ( "
    sql += " select * from xx_general_asset_equip_list_t b "
    sql += " where b.asset_no = a.asset_no "
    sql += " and b.check_month= '" + preMonth + "' "
    sql += " ) "
    sql += " and asset_type = 'A' "
    sql += " group by substr(asset_no,1,1)) b "
    sql += " where a.n1 = b.n1(+) "
    callbackFn(res, oracle.connChaos, { sql })
  })

  return router
}
